options compress=YES;
libname manasi ''; 

* START: KID WITH EACH SIBLING (AND THEIR APP INFO) IN LONG FORM, 
	HAVING ELIMINATED OWN OBS AND DUPLICATE SIBLINGS;
* END: KID WITH ALL SIBLINGS IN WIDE FORM (ONE OBS PER KID);

* CREATE ID VARIABLE FOR SIBS;
data manasi.kids_comb_wsibapps_wide;
	set manasi.kids_comb_wsibapps_long;
	sib_id + 1;
	by hun;
	if first.hun then sib_id=1;
run;

* SEE HOW MANY sibs EACH kid HAS ;
proc means data=manasi.kids_comb_wsibapps_wide noprint;
	by hun;
	var sib_id;
	output out=manasi.kids_comb_wsibapps_sibid max(sib_id)=max_sibid;
run;

proc means mean median min max p75 p90 p95 p99 data=manasi.kids_comb_wsibapps_sibid;
	var max_sibid;
run;

* DELETE sibs with number greater than 7;
data manasi.kids_comb_wsibapps_wide;
	set manasi.kids_comb_wsibapps_wide;
	if sib_id<=7;
run;

* COLLAPSE TO HUN LEVEL TO GET ONE RECORD FOR EACH kid (with all of his/her 
	sibs' apps);
data manasi.kids_comb_wsibapps_wide;
	set manasi.kids_comb_wsibapps_wide;
	by hun;

	keep 	hun 
			sibssn1-sibssn7
			appdate1_1-appdate1_7
			appdate2_1-appdate2_7
			appdate3_1-appdate3_7
			appdate4_1-appdate4_7
			appdate5_1-appdate5_7
			appdate6_1-appdate6_7
			appdate7_1-appdate7_7
			;	

	retain 	
			sibssn1-sibssn7
			appdate1_1-appdate1_7
			appdate2_1-appdate2_7
			appdate3_1-appdate3_7
			appdate4_1-appdate4_7
			appdate5_1-appdate5_7
			appdate6_1-appdate6_7
			appdate7_1-appdate7_7
			;

	array 	asibssn(1:7) $ 9	sibssn1-sibssn7 ; 
	array 	aappdate1(1:7)		appdate1_1-appdate1_7;
	array 	aappdate2(1:7)		appdate2_1-appdate2_7;
	array 	aappdate3(1:7)		appdate3_1-appdate3_7;
	array 	aappdate4(1:7)		appdate4_1-appdate4_7;
	array 	aappdate5(1:7)		appdate5_1-appdate5_7;
	array 	aappdate6(1:7)		appdate6_1-appdate6_7;
	array 	aappdate7(1:7)		appdate7_1-appdate7_7;

	if first.hun then
	do;
		do i=1 to 7;
			asibssn(i)="";
			aappdate1(i) = .;
			aappdate2(i) = .;
			aappdate3(i) = .;
			aappdate4(i) = .;
			aappdate5(i) = .;
			aappdate6(i) = .;
			aappdate7(i) = .;
		end;
	end;

	asibssn(sib_id) = sibssn ;	
	aappdate1(sib_id) = appdate1;
	aappdate2(sib_id) = appdate2;
	aappdate3(sib_id) = appdate3;
	aappdate4(sib_id) = appdate4;
	aappdate5(sib_id) = appdate5;
	aappdate6(sib_id) = appdate6;
	aappdate7(sib_id) = appdate7;

	if last.hun then output;

run;

* Create variables: # of sibling apps in year X, # approved sibling apps, 
	# denied sibling apps;
data manasi.kids_comb_wsibapps_wide;
	set manasi.kids_comb_wsibapps_wide;

	format appdate1_1-appdate1_7 date9.;
	format appdate2_1-appdate2_7 date9.;
	format appdate3_1-appdate3_7 date9.;
	format appdate4_1-appdate4_7 date9.;
	format appdate5_1-appdate5_7 date9.;
	format appdate6_1-appdate6_7 date9.;
	format appdate7_1-appdate7_7 date9.;
run;

data manasi.kids_comb_wsibapps_wide;
	set manasi.kids_comb_wsibapps_wide;

	array appdate(1:7, 1:7) appdate1_1-appdate1_7
							appdate2_1-appdate2_7
							appdate3_1-appdate3_7
							appdate4_1-appdate4_7
							appdate5_1-appdate5_7
							appdate6_1-appdate6_7
							appdate7_1-appdate7_7;


	%macro sibapp(year);
	n_sibappnum_&year.=0;
	do i = 1 to 7;
		do j = 1 to 7;
			if year(appdate(i,j))=&year. then n_sibappnum_&year.=n_sibappnum_&year.+1;
				else n_sibappnum_&year.=n_sibappnum_&year.;
		end;
	end;
	
	%mend sibapp;

	%sibapp(1992);
	%sibapp(1993);
	%sibapp(1994);
	%sibapp(1995);
	%sibapp(1996);
	%sibapp(1997);
	%sibapp(1998);
	%sibapp(1999);
	%sibapp(2000);
	%sibapp(2001);
	%sibapp(2002);
	%sibapp(2003);
	%sibapp(2004);
	%sibapp(2005);
	%sibapp(2006);
	%sibapp(2007);
	%sibapp(2008);
	%sibapp(2009);
	%sibapp(2010);
	%sibapp(2011);
	%sibapp(2012);

run;
